{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 原创：周志鹏\n",
    "### 公众号：数据不吹牛，更多案例和有趣分析等你来撩"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "os.chdir('C:\\\\Users\\\\Administrator\\\\Desktop\\\\JC数据集')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## STEP 1.数据概览"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>品牌名称</th>\n",
       "      <th>买家昵称</th>\n",
       "      <th>付款日期</th>\n",
       "      <th>订单状态</th>\n",
       "      <th>实付金额</th>\n",
       "      <th>邮费</th>\n",
       "      <th>省份</th>\n",
       "      <th>城市</th>\n",
       "      <th>购买数量</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>数据不吹牛</td>\n",
       "      <td>叫我李2</td>\n",
       "      <td>2019-01-01 00:17:59</td>\n",
       "      <td>交易成功</td>\n",
       "      <td>186</td>\n",
       "      <td>6</td>\n",
       "      <td>上海</td>\n",
       "      <td>上海市</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>数据不吹牛</td>\n",
       "      <td>0cyb1992</td>\n",
       "      <td>2019-01-01 00:59:54</td>\n",
       "      <td>交易成功</td>\n",
       "      <td>145</td>\n",
       "      <td>0</td>\n",
       "      <td>广东省</td>\n",
       "      <td>广州市</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>数据不吹牛</td>\n",
       "      <td>萝污萌莉</td>\n",
       "      <td>2019-01-01 07:48:48</td>\n",
       "      <td>交易成功</td>\n",
       "      <td>194</td>\n",
       "      <td>8</td>\n",
       "      <td>山东省</td>\n",
       "      <td>东营市</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>数据不吹牛</td>\n",
       "      <td>atblovemyy</td>\n",
       "      <td>2019-01-01 09:15:49</td>\n",
       "      <td>付款以后用户退款成功，交易自动关闭</td>\n",
       "      <td>84</td>\n",
       "      <td>0</td>\n",
       "      <td>江苏省</td>\n",
       "      <td>镇江市</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>数据不吹牛</td>\n",
       "      <td>小星期鱼</td>\n",
       "      <td>2019-01-01 09:59:33</td>\n",
       "      <td>付款以后用户退款成功，交易自动关闭</td>\n",
       "      <td>74</td>\n",
       "      <td>0</td>\n",
       "      <td>上海</td>\n",
       "      <td>上海市</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    品牌名称        买家昵称                付款日期               订单状态  实付金额  邮费   省份  \\\n",
       "0  数据不吹牛        叫我李2 2019-01-01 00:17:59               交易成功   186   6   上海   \n",
       "1  数据不吹牛    0cyb1992 2019-01-01 00:59:54               交易成功   145   0  广东省   \n",
       "2  数据不吹牛        萝污萌莉 2019-01-01 07:48:48               交易成功   194   8  山东省   \n",
       "3  数据不吹牛  atblovemyy 2019-01-01 09:15:49  付款以后用户退款成功，交易自动关闭    84   0  江苏省   \n",
       "4  数据不吹牛        小星期鱼 2019-01-01 09:59:33  付款以后用户退款成功，交易自动关闭    74   0   上海   \n",
       "\n",
       "    城市  购买数量  \n",
       "0  上海市     1  \n",
       "1  广州市     1  \n",
       "2  东营市     1  \n",
       "3  镇江市     1  \n",
       "4  上海市     1  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('PYTHON-RFM实战数据.xlsx')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 查看交易状态"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['交易成功', '付款以后用户退款成功，交易自动关闭'], dtype=object)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['订单状态'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 查看数据类型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 28833 entries, 0 to 28832\n",
      "Data columns (total 9 columns):\n",
      "品牌名称    28833 non-null object\n",
      "买家昵称    28833 non-null object\n",
      "付款日期    28833 non-null datetime64[ns]\n",
      "订单状态    28833 non-null object\n",
      "实付金额    28833 non-null int64\n",
      "邮费      28833 non-null int64\n",
      "省份      28833 non-null object\n",
      "城市      28832 non-null object\n",
      "购买数量    28833 non-null int64\n",
      "dtypes: datetime64[ns](1), int64(3), object(5)\n",
      "memory usage: 2.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## STEP 2.数据清洗"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "#### 剔除退款"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "剔除退款后还剩:27793行\n"
     ]
    }
   ],
   "source": [
    "df = df.loc[df['订单状态'] == '交易成功',:]\n",
    "print('剔除退款后还剩:%d行' % len(df))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 关键字段提取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>买家昵称</th>\n",
       "      <th>付款日期</th>\n",
       "      <th>实付金额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>叫我李2</td>\n",
       "      <td>2019-01-01 00:17:59</td>\n",
       "      <td>186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0cyb1992</td>\n",
       "      <td>2019-01-01 00:59:54</td>\n",
       "      <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>萝污萌莉</td>\n",
       "      <td>2019-01-01 07:48:48</td>\n",
       "      <td>194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>重碎叠</td>\n",
       "      <td>2019-01-01 10:00:07</td>\n",
       "      <td>197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>iho_jann</td>\n",
       "      <td>2019-01-01 10:00:08</td>\n",
       "      <td>168</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       买家昵称                付款日期  实付金额\n",
       "0      叫我李2 2019-01-01 00:17:59   186\n",
       "1  0cyb1992 2019-01-01 00:59:54   145\n",
       "2      萝污萌莉 2019-01-01 07:48:48   194\n",
       "5       重碎叠 2019-01-01 10:00:07   197\n",
       "6  iho_jann 2019-01-01 10:00:08   168"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df[['买家昵称','付款日期','实付金额']]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### R值构造"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>买家昵称</th>\n",
       "      <th>付款日期</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.blue_ram</td>\n",
       "      <td>2019-02-04 17:49:34.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>.christiny</td>\n",
       "      <td>2019-01-29 14:17:15.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>.willn1</td>\n",
       "      <td>2019-01-11 03:46:18.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>.托托m</td>\n",
       "      <td>2019-01-11 02:26:33.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000妮</td>\n",
       "      <td>2019-06-28 16:53:26.458</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         买家昵称                    付款日期\n",
       "0   .blue_ram 2019-02-04 17:49:34.000\n",
       "1  .christiny 2019-01-29 14:17:15.000\n",
       "2     .willn1 2019-01-11 03:46:18.000\n",
       "3        .托托m 2019-01-11 02:26:33.000\n",
       "4       0000妮 2019-06-28 16:53:26.458"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r = df.groupby('买家昵称')['付款日期'].max().reset_index()\n",
    "r.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>买家昵称</th>\n",
       "      <th>R</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.blue_ram</td>\n",
       "      <td>146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>.christiny</td>\n",
       "      <td>152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>.willn1</td>\n",
       "      <td>170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>.托托m</td>\n",
       "      <td>170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000妮</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         买家昵称    R\n",
       "0   .blue_ram  146\n",
       "1  .christiny  152\n",
       "2     .willn1  170\n",
       "3        .托托m  170\n",
       "4       0000妮    2"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r['R'] = (pd.to_datetime('2019-7-1') - r['付款日期']).dt.days\n",
    "r = r[['买家昵称','R']]\n",
    "r.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### F值构造"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>买家昵称</th>\n",
       "      <th>F</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.blue_ram</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>.christiny</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>.willn1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>.托托m</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000妮</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         买家昵称  F\n",
       "0   .blue_ram  1\n",
       "1  .christiny  1\n",
       "2     .willn1  1\n",
       "3        .托托m  1\n",
       "4       0000妮  1"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#引入日期标签辅助列\n",
    "df['日期标签'] = df['付款日期'].astype(str).str[:10]\n",
    "\n",
    "#把单个用户一天内订单合并\n",
    "dup_f = df.groupby(['买家昵称','日期标签'])['付款日期'].count().reset_index()\n",
    "\n",
    "#对合并后的用户统计频次\n",
    "f = dup_f.groupby('买家昵称')['付款日期'].count().reset_index()\n",
    "f.columns = ['买家昵称','F']\n",
    "f.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### M值构造"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>买家昵称</th>\n",
       "      <th>总支付金额</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.blue_ram</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>.christiny</td>\n",
       "      <td>183</td>\n",
       "      <td>1</td>\n",
       "      <td>183.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>.willn1</td>\n",
       "      <td>34</td>\n",
       "      <td>1</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>.托托m</td>\n",
       "      <td>37</td>\n",
       "      <td>1</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000妮</td>\n",
       "      <td>164</td>\n",
       "      <td>1</td>\n",
       "      <td>164.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         买家昵称  总支付金额  F      M\n",
       "0   .blue_ram     49  1   49.0\n",
       "1  .christiny    183  1  183.0\n",
       "2     .willn1     34  1   34.0\n",
       "3        .托托m     37  1   37.0\n",
       "4       0000妮    164  1  164.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum_m = df.groupby('买家昵称')['实付金额'].sum().reset_index()\n",
    "sum_m.columns = ['买家昵称','总支付金额']\n",
    "com_m = pd.merge(sum_m,f,left_on = '买家昵称',right_on = '买家昵称',how = 'inner')\n",
    "\n",
    "#计算用户平均支付金额\n",
    "com_m['M'] = com_m['总支付金额'] / com_m['F']\n",
    "com_m.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 三个值合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>买家昵称</th>\n",
       "      <th>R</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.blue_ram</td>\n",
       "      <td>146</td>\n",
       "      <td>1</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>.christiny</td>\n",
       "      <td>152</td>\n",
       "      <td>1</td>\n",
       "      <td>183.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>.willn1</td>\n",
       "      <td>170</td>\n",
       "      <td>1</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>.托托m</td>\n",
       "      <td>170</td>\n",
       "      <td>1</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000妮</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>164.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         买家昵称    R  F      M\n",
       "0   .blue_ram  146  1   49.0\n",
       "1  .christiny  152  1  183.0\n",
       "2     .willn1  170  1   34.0\n",
       "3        .托托m  170  1   37.0\n",
       "4       0000妮    2  1  164.0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm = pd.merge(r,com_m,left_on = '买家昵称',right_on = '买家昵称',how = 'inner')\n",
    "rfm = rfm[['买家昵称','R','F','M']]\n",
    "rfm.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## STEP 3.维度确认（不涉及代码故省略）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## STEP 4.分值计算"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### R值计算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>买家昵称</th>\n",
       "      <th>R</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th>R-SCORE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.blue_ram</td>\n",
       "      <td>146</td>\n",
       "      <td>1</td>\n",
       "      <td>49.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>.christiny</td>\n",
       "      <td>152</td>\n",
       "      <td>1</td>\n",
       "      <td>183.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>.willn1</td>\n",
       "      <td>170</td>\n",
       "      <td>1</td>\n",
       "      <td>34.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>.托托m</td>\n",
       "      <td>170</td>\n",
       "      <td>1</td>\n",
       "      <td>37.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000妮</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>164.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         买家昵称    R  F      M  R-SCORE\n",
       "0   .blue_ram  146  1   49.0      1.0\n",
       "1  .christiny  152  1  183.0      1.0\n",
       "2     .willn1  170  1   34.0      1.0\n",
       "3        .托托m  170  1   37.0      1.0\n",
       "4       0000妮    2  1  164.0      5.0"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm['R-SCORE'] = pd.cut(rfm['R'],bins = [0,30,60,90,120,1000000],labels = [5,4,3,2,1],right = False).astype(float)\n",
    "rfm.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### F、M值计算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>买家昵称</th>\n",
       "      <th>R</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th>R-SCORE</th>\n",
       "      <th>F-SCORE</th>\n",
       "      <th>M-SCORE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.blue_ram</td>\n",
       "      <td>146</td>\n",
       "      <td>1</td>\n",
       "      <td>49.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>.christiny</td>\n",
       "      <td>152</td>\n",
       "      <td>1</td>\n",
       "      <td>183.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>.willn1</td>\n",
       "      <td>170</td>\n",
       "      <td>1</td>\n",
       "      <td>34.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>.托托m</td>\n",
       "      <td>170</td>\n",
       "      <td>1</td>\n",
       "      <td>37.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000妮</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>164.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         买家昵称    R  F      M  R-SCORE  F-SCORE  M-SCORE\n",
       "0   .blue_ram  146  1   49.0      1.0      1.0      1.0\n",
       "1  .christiny  152  1  183.0      1.0      1.0      4.0\n",
       "2     .willn1  170  1   34.0      1.0      1.0      1.0\n",
       "3        .托托m  170  1   37.0      1.0      1.0      1.0\n",
       "4       0000妮    2  1  164.0      5.0      1.0      4.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm['F-SCORE'] = pd.cut(rfm['F'],bins = [1,2,3,4,5,1000000],labels = [1,2,3,4,5],right = False).astype(float)\n",
    "rfm['M-SCORE'] = pd.cut(rfm['M'],bins = [0,50,100,150,200,1000000],labels = [1,2,3,4,5],right = False).astype(float)\n",
    "rfm.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 和平均值对比，减少客户分类数量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>买家昵称</th>\n",
       "      <th>R</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th>R-SCORE</th>\n",
       "      <th>F-SCORE</th>\n",
       "      <th>M-SCORE</th>\n",
       "      <th>R是否大于均值</th>\n",
       "      <th>F是否大于均值</th>\n",
       "      <th>M是否大于均值</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.blue_ram</td>\n",
       "      <td>146</td>\n",
       "      <td>1</td>\n",
       "      <td>49.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>.christiny</td>\n",
       "      <td>152</td>\n",
       "      <td>1</td>\n",
       "      <td>183.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>.willn1</td>\n",
       "      <td>170</td>\n",
       "      <td>1</td>\n",
       "      <td>34.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>.托托m</td>\n",
       "      <td>170</td>\n",
       "      <td>1</td>\n",
       "      <td>37.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000妮</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>164.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         买家昵称    R  F      M  R-SCORE  F-SCORE  M-SCORE  R是否大于均值  F是否大于均值  \\\n",
       "0   .blue_ram  146  1   49.0      1.0      1.0      1.0        0        0   \n",
       "1  .christiny  152  1  183.0      1.0      1.0      4.0        0        0   \n",
       "2     .willn1  170  1   34.0      1.0      1.0      1.0        0        0   \n",
       "3        .托托m  170  1   37.0      1.0      1.0      1.0        0        0   \n",
       "4       0000妮    2  1  164.0      5.0      1.0      4.0        1        0   \n",
       "\n",
       "   M是否大于均值  \n",
       "0        0  \n",
       "1        1  \n",
       "2        0  \n",
       "3        0  \n",
       "4        1  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm['R是否大于均值'] = (rfm['R-SCORE'] > rfm['R-SCORE'].mean()) * 1\n",
    "rfm['F是否大于均值'] = (rfm['F-SCORE'] > rfm['F-SCORE'].mean()) * 1\n",
    "rfm['M是否大于均值'] = (rfm['M-SCORE'] > rfm['M-SCORE'].mean()) * 1\n",
    "rfm.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## STEP 5.客户分层"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 构建合并指标"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>买家昵称</th>\n",
       "      <th>R</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th>R-SCORE</th>\n",
       "      <th>F-SCORE</th>\n",
       "      <th>M-SCORE</th>\n",
       "      <th>R是否大于均值</th>\n",
       "      <th>F是否大于均值</th>\n",
       "      <th>M是否大于均值</th>\n",
       "      <th>人群数值</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.blue_ram</td>\n",
       "      <td>146</td>\n",
       "      <td>1</td>\n",
       "      <td>49.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>.christiny</td>\n",
       "      <td>152</td>\n",
       "      <td>1</td>\n",
       "      <td>183.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>.willn1</td>\n",
       "      <td>170</td>\n",
       "      <td>1</td>\n",
       "      <td>34.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>.托托m</td>\n",
       "      <td>170</td>\n",
       "      <td>1</td>\n",
       "      <td>37.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000妮</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>164.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         买家昵称    R  F      M  R-SCORE  F-SCORE  M-SCORE  R是否大于均值  F是否大于均值  \\\n",
       "0   .blue_ram  146  1   49.0      1.0      1.0      1.0        0        0   \n",
       "1  .christiny  152  1  183.0      1.0      1.0      4.0        0        0   \n",
       "2     .willn1  170  1   34.0      1.0      1.0      1.0        0        0   \n",
       "3        .托托m  170  1   37.0      1.0      1.0      1.0        0        0   \n",
       "4       0000妮    2  1  164.0      5.0      1.0      4.0        1        0   \n",
       "\n",
       "   M是否大于均值  人群数值  \n",
       "0        0     0  \n",
       "1        1     1  \n",
       "2        0     0  \n",
       "3        0     0  \n",
       "4        1   101  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm['人群数值'] = (rfm['R是否大于均值'] * 100) + (rfm['F是否大于均值'] * 10) + (rfm['M是否大于均值'] * 1)\n",
    "rfm.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 基于指标给客户打标签"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#判断R/F/M是否大于均值\n",
    "def transform_label(x):\n",
    "    if x == 111:\n",
    "        label = '重要价值客户'\n",
    "    elif x == 110:\n",
    "        label = '消费潜力客户'\n",
    "    elif x == 101:\n",
    "        label = '频次深耕客户'\n",
    "    elif x == 100:\n",
    "        label = '新客户'\n",
    "    elif x == 11:\n",
    "        label = '重要价值流失预警客户'\n",
    "    elif x == 10:\n",
    "        label = '一般客户'\n",
    "    elif x == 1:\n",
    "        label = '高消费唤回客户'\n",
    "    elif x == 0:\n",
    "        label = '流失客户'\n",
    "    return label"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 标签应用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>买家昵称</th>\n",
       "      <th>R</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th>R-SCORE</th>\n",
       "      <th>F-SCORE</th>\n",
       "      <th>M-SCORE</th>\n",
       "      <th>R是否大于均值</th>\n",
       "      <th>F是否大于均值</th>\n",
       "      <th>M是否大于均值</th>\n",
       "      <th>人群数值</th>\n",
       "      <th>人群类型</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.blue_ram</td>\n",
       "      <td>146</td>\n",
       "      <td>1</td>\n",
       "      <td>49.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>流失客户</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>.christiny</td>\n",
       "      <td>152</td>\n",
       "      <td>1</td>\n",
       "      <td>183.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>高消费唤回客户</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>.willn1</td>\n",
       "      <td>170</td>\n",
       "      <td>1</td>\n",
       "      <td>34.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>流失客户</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>.托托m</td>\n",
       "      <td>170</td>\n",
       "      <td>1</td>\n",
       "      <td>37.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>流失客户</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000妮</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>164.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>101</td>\n",
       "      <td>频次深耕客户</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         买家昵称    R  F      M  R-SCORE  F-SCORE  M-SCORE  R是否大于均值  F是否大于均值  \\\n",
       "0   .blue_ram  146  1   49.0      1.0      1.0      1.0        0        0   \n",
       "1  .christiny  152  1  183.0      1.0      1.0      4.0        0        0   \n",
       "2     .willn1  170  1   34.0      1.0      1.0      1.0        0        0   \n",
       "3        .托托m  170  1   37.0      1.0      1.0      1.0        0        0   \n",
       "4       0000妮    2  1  164.0      5.0      1.0      4.0        1        0   \n",
       "\n",
       "   M是否大于均值  人群数值     人群类型  \n",
       "0        0     0     流失客户  \n",
       "1        1     1  高消费唤回客户  \n",
       "2        0     0     流失客户  \n",
       "3        0     0     流失客户  \n",
       "4        1   101   频次深耕客户  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm['人群类型'] = rfm['人群数值'].apply(transform_label)\n",
    "rfm.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 人数统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>客户类型</th>\n",
       "      <th>人数</th>\n",
       "      <th>人数占比</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>高消费唤回客户</td>\n",
       "      <td>7338</td>\n",
       "      <td>0.288670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>流失客户</td>\n",
       "      <td>6680</td>\n",
       "      <td>0.262785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>频次深耕客户</td>\n",
       "      <td>5427</td>\n",
       "      <td>0.213493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>新客户</td>\n",
       "      <td>4224</td>\n",
       "      <td>0.166168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>重要价值客户</td>\n",
       "      <td>756</td>\n",
       "      <td>0.029740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>消费潜力客户</td>\n",
       "      <td>450</td>\n",
       "      <td>0.017703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>重要价值流失预警客户</td>\n",
       "      <td>360</td>\n",
       "      <td>0.014162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>一般客户</td>\n",
       "      <td>185</td>\n",
       "      <td>0.007278</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         客户类型    人数      人数占比\n",
       "0     高消费唤回客户  7338  0.288670\n",
       "1        流失客户  6680  0.262785\n",
       "2      频次深耕客户  5427  0.213493\n",
       "3         新客户  4224  0.166168\n",
       "4      重要价值客户   756  0.029740\n",
       "5      消费潜力客户   450  0.017703\n",
       "6  重要价值流失预警客户   360  0.014162\n",
       "7        一般客户   185  0.007278"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count = rfm['人群类型'].value_counts().reset_index()\n",
    "count.columns = ['客户类型','人数']\n",
    "count['人数占比'] = count['人数'] / count['人数'].sum()\n",
    "count"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 金额统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>客户类型</th>\n",
       "      <th>消费金额</th>\n",
       "      <th>金额占比</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>一般客户</td>\n",
       "      <td>25803.0</td>\n",
       "      <td>0.007349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>新客户</td>\n",
       "      <td>270869.0</td>\n",
       "      <td>0.077142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>流失客户</td>\n",
       "      <td>444617.0</td>\n",
       "      <td>0.126624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>消费潜力客户</td>\n",
       "      <td>64075.0</td>\n",
       "      <td>0.018248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>重要价值客户</td>\n",
       "      <td>269230.0</td>\n",
       "      <td>0.076675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>重要价值流失预警客户</td>\n",
       "      <td>116665.0</td>\n",
       "      <td>0.033226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>频次深耕客户</td>\n",
       "      <td>981893.0</td>\n",
       "      <td>0.279638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>高消费唤回客户</td>\n",
       "      <td>1338153.0</td>\n",
       "      <td>0.381098</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         客户类型       消费金额      金额占比\n",
       "0        一般客户    25803.0  0.007349\n",
       "1         新客户   270869.0  0.077142\n",
       "2        流失客户   444617.0  0.126624\n",
       "3      消费潜力客户    64075.0  0.018248\n",
       "4      重要价值客户   269230.0  0.076675\n",
       "5  重要价值流失预警客户   116665.0  0.033226\n",
       "6      频次深耕客户   981893.0  0.279638\n",
       "7     高消费唤回客户  1338153.0  0.381098"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfm['购买总金额'] = rfm['F'] * rfm['M']\n",
    "mon = rfm.groupby('人群类型')['购买总金额'].sum().reset_index()\n",
    "mon.columns = ['客户类型','消费金额']\n",
    "mon['金额占比'] = mon['消费金额'] / mon['消费金额'].sum()\n",
    "mon"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = pd.merge(count,mon,left_on = '客户类型',right_on = '客户类型')\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 模型封装，一个回车就能返回结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#输入源数据文件名\n",
    "def get_rfm(name = 'PYTHON-RFM实战数据.xlsx'):\n",
    "    df = pd.read_excel(name)\n",
    "    df = df.loc[df['订单状态'] == '交易成功',:]\n",
    "    print('剔除退款后还剩:%d行' % len(df))\n",
    "    df = df[['买家昵称','付款日期','实付金额']]\n",
    "\n",
    "    r = df.groupby('买家昵称')['付款日期'].max().reset_index()\n",
    "    r['R'] = (pd.to_datetime('2019-7-1') - r['付款日期']).dt.days\n",
    "    r = r[['买家昵称','R']]\n",
    "\n",
    "    #引入日期标签辅助列\n",
    "    df['日期标签'] = df['付款日期'].astype(str).str[:10]\n",
    "\n",
    "    #把单个用户一天内订单合并\n",
    "    dup_f = df.groupby(['买家昵称','日期标签'])['付款日期'].count().reset_index()\n",
    "\n",
    "    #对合并后的用户统计频次\n",
    "    f = dup_f.groupby('买家昵称')['付款日期'].count().reset_index()\n",
    "    f.columns = ['买家昵称','F']\n",
    "\n",
    "    sum_m = df.groupby('买家昵称')['实付金额'].sum().reset_index()\n",
    "    sum_m.columns = ['买家昵称','总支付金额']\n",
    "    com_m = pd.merge(sum_m,f,left_on = '买家昵称',right_on = '买家昵称',how = 'inner')\n",
    "\n",
    "    #计算用户平均支付金额\n",
    "    com_m['M'] = com_m['总支付金额'] / com_m['F']\n",
    "\n",
    "    rfm = pd.merge(r,com_m,left_on = '买家昵称',right_on = '买家昵称',how = 'inner')\n",
    "    rfm = rfm[['买家昵称','R','F','M']]\n",
    "\n",
    "\n",
    "    rfm['R-SCORE'] = pd.cut(rfm['R'],bins = [0,30,60,90,120,1000000],labels = [5,4,3,2,1],right = False).astype(float)\n",
    "    rfm['F-SCORE'] = pd.cut(rfm['F'],bins = [1,2,3,4,5,1000000],labels = [1,2,3,4,5],right = False).astype(float)\n",
    "    rfm['M-SCORE'] = pd.cut(rfm['M'],bins = [0,50,100,150,200,1000000],labels = [1,2,3,4,5],right = False).astype(float)\n",
    "\n",
    "    rfm['R是否大于均值'] = (rfm['R-SCORE'] > rfm['R-SCORE'].mean()) * 1\n",
    "    rfm['F是否大于均值'] = (rfm['F-SCORE'] > rfm['F-SCORE'].mean()) * 1\n",
    "    rfm['M是否大于均值'] = (rfm['M-SCORE'] > rfm['M-SCORE'].mean()) * 1\n",
    "\n",
    "    rfm['人群数值'] = (rfm['R是否大于均值'] * 100) + (rfm['F是否大于均值'] * 10) + (rfm['M是否大于均值'] * 1)\n",
    "\n",
    "    rfm['人群类型'] = rfm['人群数值'].apply(transform_label)\n",
    "\n",
    "    count = rfm['人群类型'].value_counts().reset_index()\n",
    "    count.columns = ['客户类型','人数']\n",
    "    count['人数占比'] = count['人数'] / count['人数'].sum()\n",
    "\n",
    "    rfm['购买总金额'] = rfm['F'] * rfm['M']\n",
    "    mon = rfm.groupby('人群类型')['购买总金额'].sum().reset_index()\n",
    "    mon.columns = ['客户类型','消费金额']\n",
    "    mon['金额占比'] = mon['消费金额'] / mon['消费金额'].sum()\n",
    "\n",
    "    result = pd.merge(count,mon,left_on = '客户类型',right_on = '客户类型')\n",
    "\n",
    "    return result\n",
    "\n",
    "\n",
    "#判断R/F/M是否大于均值\n",
    "def transform_label(x):\n",
    "    if x == 111:\n",
    "        label = '重要价值客户'\n",
    "    elif x == 110:\n",
    "        label = '消费潜力客户'\n",
    "    elif x == 101:\n",
    "        label = '频次深耕客户'\n",
    "    elif x == 100:\n",
    "        label = '新客户'\n",
    "    elif x == 11:\n",
    "        label = '重要价值流失预警客户'\n",
    "    elif x == 10:\n",
    "        label = '一般客户'\n",
    "    elif x == 1:\n",
    "        label = '高消费唤回客户'\n",
    "    elif x == 0:\n",
    "        label = '流失客户'\n",
    "    return label"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "剔除退款后还剩:27793行\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>客户类型</th>\n",
       "      <th>人数</th>\n",
       "      <th>人数占比</th>\n",
       "      <th>消费金额</th>\n",
       "      <th>金额占比</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>高消费唤回客户</td>\n",
       "      <td>7338</td>\n",
       "      <td>0.288670</td>\n",
       "      <td>1338153.0</td>\n",
       "      <td>0.381098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>流失客户</td>\n",
       "      <td>6680</td>\n",
       "      <td>0.262785</td>\n",
       "      <td>444617.0</td>\n",
       "      <td>0.126624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>频次深耕客户</td>\n",
       "      <td>5427</td>\n",
       "      <td>0.213493</td>\n",
       "      <td>981893.0</td>\n",
       "      <td>0.279638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>新客户</td>\n",
       "      <td>4224</td>\n",
       "      <td>0.166168</td>\n",
       "      <td>270869.0</td>\n",
       "      <td>0.077142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>重要价值客户</td>\n",
       "      <td>756</td>\n",
       "      <td>0.029740</td>\n",
       "      <td>269230.0</td>\n",
       "      <td>0.076675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>消费潜力客户</td>\n",
       "      <td>450</td>\n",
       "      <td>0.017703</td>\n",
       "      <td>64075.0</td>\n",
       "      <td>0.018248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>重要价值流失预警客户</td>\n",
       "      <td>360</td>\n",
       "      <td>0.014162</td>\n",
       "      <td>116665.0</td>\n",
       "      <td>0.033226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>一般客户</td>\n",
       "      <td>185</td>\n",
       "      <td>0.007278</td>\n",
       "      <td>25803.0</td>\n",
       "      <td>0.007349</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         客户类型    人数      人数占比       消费金额      金额占比\n",
       "0     高消费唤回客户  7338  0.288670  1338153.0  0.381098\n",
       "1        流失客户  6680  0.262785   444617.0  0.126624\n",
       "2      频次深耕客户  5427  0.213493   981893.0  0.279638\n",
       "3         新客户  4224  0.166168   270869.0  0.077142\n",
       "4      重要价值客户   756  0.029740   269230.0  0.076675\n",
       "5      消费潜力客户   450  0.017703    64075.0  0.018248\n",
       "6  重要价值流失预警客户   360  0.014162   116665.0  0.033226\n",
       "7        一般客户   185  0.007278    25803.0  0.007349"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res = get_rfm(name = 'PYTHON-RFM实战数据.xlsx')\n",
    "res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
